package com.wcb.test.service;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Service;

import com.wcb.test.bean.PageBean;
import com.wcb.test.bean.Product;
import com.wcb.test.bean.SaleRecord;
import com.wcb.test.bean.User;
import com.wcb.test.util.DateUtil;

/**
 * @desc 数据查询
 * @author knight Wang
 * @Date 2014-5-2
 */

@Service
public class QueryDataService extends BaseService {
	
//	private Log log = LogFactory.getLog(QueryDataService.class);
	
	/**
	 * 返回用户的分页对象
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	public PageBean<User> getUserPage(int pageNo,int pageSize){
		
//		String sql1 = " SELECT SUM(t.price*t.num) from t_sale_record t WHERE t.product = 1";
//		double d =  (Double)simpleDao.queryForObject(sql1);
//		System.out.println(d);
		
		String sql = "select * from t_user";
		return getPageBeans(sql, pageNo, pageSize);
	}
	
	
	/**
	 * 返回产品的分页对象
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	public PageBean<Product> getProductPage(int pageNo,int pageSize){
		
		String sql = "select * from t_product";
		return getPageBeans(sql, pageNo, pageSize);
	}
	
	
	
	public PageBean<SaleRecord> getSaleRecordPage(int pageNo,int pageSize,int uid,int pid){
		
		String sql = "select * from t_sale_record where 1=1 ";
		List<Object> parms = new ArrayList<Object>();
		if(uid!=0){
			sql += " and user = ? ";
			parms.add(uid);
		}
		if(pid!=0){
			sql += " and product = ? ";
			parms.add(pid);
		}
		return getPageBeans(sql, pageNo, pageSize,parms.toArray());
	}
	
	/**
	 * 查询某个产品
	 * @param id
	 * @return  
	 * #  

	 	SELECT MAX(t.saledate) FROM t_sale_record t
	 * 
	 */
	public Product getProductById(int id){
		
		return simpleDao.getById(Product.class, id);
	}
	
	/**
	 * @return 最新月份的月初时间
	 */
	public Date getMaxMothStartDate(){
		String sql = "SELECT MAX(t.saledate) FROM t_sale_record t";
		Date maxDate = (Date) simpleDao.queryForObject(sql);
		return DateUtil.getMonthStartDate(maxDate);
	}
	
	/**
	 * 查询某个时间段topSales
	 * @param startDate
	 * @param endDate
	 * @return
	 * 
	 * SELECT p.pname,SUM((t.price-p.cost)*t.num) as profit FROM
 		t_sale_record t,t_product p,t_user u  where  EXISTS (select * from t_user u1 where  u1.id = t.user and u1.address = "湖南") and t.product = p.id  GROUP BY t.product 
	 */
	public List<Map<String, Object>> getTopProfitProductList(String address,Date startDate,Date endDate){
		String sql = "SELECT p.pname,SUM((t.price-p.cost)*t.num) as profit FROM t_sale_record t,t_product p,t_user u"
				+ " where EXISTS (select * from t_user u1 where  u1.id = t.user and u1.address = ?) "
				+ " and t.product = p.id and t.saledate BETWEEN ? and ? GROUP BY t.product ORDER BY profit DESC limit 0,10";
		
		try {
			return simpleDao.queryBySQL(sql,new Object[]{address,startDate,endDate});
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * 最近一个月topSale
	 * @return
	 */
	public List<Map<String, Object>> getMaxMonthTopProfitProductList(String address){
		//String address = "湖南";
		Date startDate = getMaxMothStartDate();
		Date endDate = DateUtil.getMonthEndDate(startDate);
		return getTopProfitProductList(address,startDate,endDate);
	}

	/**
	 * 上一个月TOP SALES
	 * @return
	 */
	public List<Map<String, Object>> getPreMonthTopProfitProductList(String address){
		//String address = "湖南";
		//这个月第一天
		Date maxStartDate = getMaxMothStartDate();
		//上个月最后一天
		Date endDate = new Date(maxStartDate.getTime()-1000l);
		
		Date startDate = DateUtil.getMonthStartDate(endDate);
		return getTopProfitProductList(address,startDate,endDate);
	}
	
	/**
	 * 根据产品ID获取该产品的客户数量
	 * @param pid
	 * @return 
	 */
	public  List<Map<String, Object>> getAddressList(){
		String sql = "SELECT DISTINCT address from t_user";
		return simpleDao.queryForList(sql);
	}
}

